﻿using System;
using System.Collections.Generic;
using SharpSoft.Data;

namespace SharpSoft.BL
{
    public class UsersManage
    {
        private Database _db;
        public Database db { get { return _db; } }
        public UsersManage(Database p_db)
        {
            _db = p_db;
        }
        /// <summary>
        /// 创建用户
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <param name="roles">设置该用户的角色</param>
        /// <param name="cellphone"></param>
        /// <param name="email"></param>
        /// <returns>创建成功则反馈用户Id，其他值：0：用户名已存在；-1：出现其他异常；</returns>
        public int CreateUser(string username, string password, int[] roles, string cellphone, string email)
        {
            string queryexists = @"SELECT [UserName] FROM Sys_Users WHERE UserName=@username";


            string insertsql = @"INSERT INTO Sys_Users(UserName,Password,Cellphone,Email,Status,CreateTime)VALUES(@username, @password, @cellphone, @email,0,getdate());";
            string querysql = @"SELECT UserId FROM Sys_Users WHERE UserName=@username;";

            int userid = -1;
            var uname = db.ExecuteScalar(new GSQL(queryexists), new { username = username });
            if (uname != null)
            {
                return 0;
            }
            db.ExecuteNonQuery(new GSQL(insertsql), new { username = username, password = password, cellphone = cellphone, email = email });
            userid = db.ExecuteInt32(new GSQL(querysql), new { username = username });
            SetUserRoles(userid, roles);
            return userid;
        }

        /// <summary>
        /// 设置用户的角色
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="roles"></param>
        public void SetUserRoles(int uid, int[] roles)
        {
            string sqldeleteroles = @"DELETE FROM Sys_User_Role_Mapping WHERE UserId=@uid;";
            db.ExecuteNonQuery(new GSQL(sqldeleteroles), new { uid = uid });
            if (roles == null || roles.Length == 0)
            {
                roles = new int[] { 0 };//默认具有普通用户角色
            }
            if (roles != null)
            {
                foreach (var rid in roles)
                {
                    string sqlinsert = "INSERT INTO Sys_User_Role_Mapping(UserId,RoleId)VALUES(@uid,@rid);";
                    db.ExecuteNonQuery(new GSQL(sqlinsert), new { uid = uid, rid = rid });
                }
            }
        }

        /// <summary>
        /// 设置用户的状态
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="status"></param>
        public void SetUserStatus(int userid, int status, string msg = null, string ip = null, string clientinfo = null)
        {
            string update = @"UPDATE [Sys_Users] SET [Status]=@status WHERE [UserId]=@userid";
            db.ExecuteNonQuery(new GSQL(update), new { status = status, userid = userid });
            UserLog(userid, status, msg, ip, clientinfo);
        }
        /// <summary>
        /// 记录用户操作日志
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="status"></param>
        /// <param name="msg"></param>
        /// <param name="ip"></param>
        /// <param name="clientinfo"></param>
        private void UserLog(int userid, int status, string msg = null, string ip = null, string clientinfo = null)
        {
            string insert = @"INSERT INTO [Sys_UserLogs]([LoginTime],[UserId],[Status],[Message],[Ip],[ClientInfo])
VALUES(@logintime,@userid,@status,@msg,@id,@clientinfo);";
            // db.FormatSql("INSERT INTO [Sys_UserLogs]([LoginTime],[UserId],[Status],[Message],[Ip],[ClientInfo]) VALUES({0},{1},{2},{3},{4},{5});", DateTime.Now, userid, status, msg, ip, clientinfo);

            db.ExecuteNonQuery(new GSQL(insert), new { logintime = DateTime.Now, userid = userid, status = status, msg = msg, ip = ip, clientinfo = clientinfo });
        }
        /// <summary>
        /// 检查用户是否具有指定的角色，不具备则抛出异常
        /// </summary>
        public void CheckUserRole(int uid, int rid)
        {
            string sql = "SELECT count(1) FROM [Sys_User_Role_Mapping] WHERE [UserId]=@uid AND [RoleId]=@rid;";
            if (db.ExecuteInt32(new GSQL(sql)) < 1)
            {
                throw new Exception("用户不具备该角色。");
            }
        }
        /// <summary>
        /// 获取用户的Id
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public int? GetUserId(string username)
        {
            string sql = "SELECT [UserId] FROM [Sys_Users] WHERE [UserName]=@username OR [Cellphone]=@username OR [Email]=@username";
            return db.ExecuteScalar<int?>(new GSQL(sql), new { username = username });
        }
        /// <summary>
        /// 匹配用户密码是否正确
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool MatchPassword(int uid, string password)
        {
            string sql = "SELECT COUNT([UserId]) FROM [Sys_Users] WHERE [UserId]=@userid AND [Password]=@pwd";
            int count = db.ExecuteInt32(new GSQL(sql), new { userid = uid, pwd = Security.MD5Hash(password) });
            return count >= 1;
        }
        /// <summary>
        /// 用户注销
        /// </summary>
        /// <param name="uid"></param>
        public void Logoff(int uid)
        {
            SetUserStatus(uid, 0, "注销");
        }

        /// <summary>
        /// 用户尝试登陆
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <param name="ip"></param>
        /// <param name="clientinfo"></param>
        /// <returns>反馈用户Id；大于0：登录成功；0:用户不存在；-1：用户密码验证失败；-2：短时间内验证失败次数过多。</returns>
        public int TryLogin(string username, string password, string ip, string clientinfo)
        {
            var uid = GetUserId(username);

            if (!uid.HasValue)
            {
                return 0;
            }
            int status;
            string sql = "SELECT [Status] FROM [Sys_Users] WHERE [UserId]=@uid;";
            status = db.ExecuteInt32(new GSQL(sql), new { uid = uid });
            if (status < -3)
            {//黑名单用户
                return status;
            }
            
            if (status == -2)
            {//禁止登录
                return -2;
            }
            var pswok = MatchPassword(uid.Value, password);
            if (pswok)
            {//密码正确
                SetUserStatus(uid.Value, 1, "登陆成功", ip, clientinfo);
                return uid.Value;
            }
            else
            {//密码验证失败
                SetUserStatus(uid.Value, -1, "密码错误", ip, clientinfo);
                return -1;
            }
        }
        /// <summary>
        /// 获取用户角色
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public int[] GetUserRoles(int uid)
        {
            string sqlgetroles = "SELECT RoleId FROM Sys_User_Role_Mapping UserId=@uid";
            var roles = db.ExecuteArray<int>(new GSQL(sqlgetroles), new { uid = uid });
            return roles;
        }
        /// <summary>
        /// 获取关系结构中的附属用户
        /// </summary>
        /// <param name="uid">主体用户</param>
        /// <param name="relations">不指定任何关系则输出所有关系</param>
        /// <returns></returns>
        public int[] GetRelationEncliticUsers(int uid, int[] relations)
        {
            string sql = "SELECT OtherUserId FROM Sys_UserRelations UserId=@uid";
            if (relations != null && relations.Length > 0)
            {
                sql += $" AND Relation IN({string.Join(",", relations)})";
            }
            return db.ExecuteArray<int>(new GSQL(sql), new { uid = uid });
        }

        /// <summary>
        /// 获取关系结构中的主体用户
        /// </summary>
        /// <param name="uid">附属用户Id</param>
        /// <param name="relations">不指定任何关系则输出所有关系</param>
        /// <returns></returns>
        public int[] GetRelationPrimaryUsers(int uid, int[] relations)
        {
            string sql = "SELECT UserId FROM Sys_UserRelations OtherUserId=@uid";
            if (relations != null && relations.Length > 0)
            {
                sql += $" AND Relation IN({string.Join(",", relations)})";
            }
            return db.ExecuteArray<int>(new GSQL(sql), new { uid = uid });
        }


        /// <summary>
        /// 获取用户权限
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDictionary<string, bool> GetPermissionsForUser(int uid)
        {
            string sqlgetuserper = @"SELECT ([ModuleName]+'_'+[ControllerName]+'_'+[ActionName]) AS [Key],Allow FROM Sys_PermissionsForUser " +
                "WHERE UserId=@uid";
            var userpers = db.ExecuteDictionary<string, bool>(new GSQL(sqlgetuserper), "Key", "Allow", new { uid = uid });
            var roles = GetUserRoles(uid);
            var rolepers = new Dictionary<string, bool>();
            foreach (var roleid in roles)
            {
                var pers = GetPermissionsForRole(roleid);
                foreach (var item in pers.Keys)
                {
                    if (rolepers.ContainsKey(item))
                    {
                        if (rolepers[item])
                        {
                            continue;
                        }
                        else
                        {
                            rolepers[item] = pers[item];
                        }

                    }
                    else
                    {
                        if (pers[item])
                        {
                            rolepers.Add(item, true);
                        }
                    }
                }
            }
            foreach (var item in rolepers)
            {
                if (!userpers.ContainsKey(item.Key))
                {
                    userpers.Add(item.Key, item.Value);
                }
            }

            return userpers;

        }
        /// <summary>
        /// 获取指定角色的权限
        /// </summary>
        /// <param name="rid"></param>
        /// <returns></returns>
        public IDictionary<string, bool> GetPermissionsForRole(int rid)
        {
            string sqlgetroleper = @"SELECT ([ModuleName]+'_'+[ControllerName]+'_'+[ActionName]) AS [Key],Allow FROM Sys_PermissionsForRole " +
                  "WHERE RoleId=@rid";
            return db.ExecuteDictionary<string, bool>(new GSQL(sqlgetroleper), "Key", "Allow", new { rid = rid });

        }
        /// <summary>
        /// 清空用户的所有重定义权限。（清除后该用户只具备其所属角色的基础权限）
        /// </summary>
        /// <param name="uid"></param>
        public void ClearUserPermissions(int uid)
        {
            string sql = "DELETE FROM [Sys_PermissionsForUser] WHERE [UserId]=@uid";
            db.ExecuteNonQuery(new GSQL(sql), new { uid = uid });
        }
        /// <summary>
        /// 设置用户权限
        /// </summary>
        public void SetUserPermissions(int uid, string modulename, string controllername, string actionname, bool allow)
        {
            string sql = 
 @"DECLARE @allow bit=NULL;
  SELECT @allow=[Allow] FROM [Sys_PermissionsForUser] WHERE [UserId]=@userid AND [ModuleName]=@modulename AND [ControllerName]=@controllername AND [ActionName]=@actionname;
  IF(@allow is NULL)
  BEGIN
  INSERT INTO [Sys_PermissionsForUser]([UserId],[ModuleName],[ControllerName],[ActionName],[Allow]) VALUES(@userid,@modulename,@controllername,@actionname,@allowv);
  END
  ELSE
  BEGIN
  UPDATE [Sys_PermissionsForUser] SET [Allow]=@allowv WHERE [UserId]=@userid AND [ModuleName]=@modulename AND [ControllerName]=@controllername AND [ActionName]=@actionname;
  END" ;
            db.ExecuteNonQuery(new GSQL(sql),new {userid= uid, modulename= modulename, controllername= controllername, actionname= actionname, allowv= allow });
        }
        /// <summary>
        /// 设置角色权限
        /// </summary>
        public void SetRolePermissions(int rid, string modulename, string controllername, string actionname, bool allow)
        {
            string sql =
  @"DECLARE @allow bit=NULL;
  SELECT @allow=[Allow] FROM [Sys_PermissionsForRole] WHERE [RoleId]=@roleid AND [ModuleName]=@modulename AND [ControllerName]=@controllername AND [ActionName]=@actionname;
  IF(@allow is NULL)
  BEGIN
  INSERT INTO [Sys_PermissionsForRole]([RoleId],[ModuleName],[ControllerName],[ActionName],[Allow]) VALUES(@roleid,@modulename,@controllername,@actionname,@allowv);
  END
  ELSE
  BEGIN
  UPDATE [Sys_PermissionsForRole] SET [Allow]=@allowv WHERE [RoleId]=@roleid AND [ModuleName]=@modulename AND [ControllerName]=@controllername AND [ActionName]=@actionname;
  END";
            db.ExecuteNonQuery(new GSQL(sql),new { roleid=rid, modulename= modulename, controllername= controllername, actionname= actionname, allowv=allow });
        }

    }
}
